/*******************************************************************************
 * Copyright (c) 2005, 2014 springside.github.io
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 *******************************************************************************/
package com.sdgakj.repository;

import com.sdgakj.entity.Donation;
import com.sdgakj.entity.Order;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import java.util.List;

public interface DonationDao extends PagingAndSortingRepository<Donation, Long>,JpaSpecificationExecutor<Donation> {
    @Query(value="select count(1) from f_donation where user_id=?1 ",nativeQuery=true)
    public Integer getCountByCustomerId(Long customerId);

    @Query(value = "select * from f_donation o where o.state=?1 and type in (2,3)", nativeQuery = true)
    public List<Donation> getOrderListByStateAndType(Integer state);

    @Query(value = "select * from f_donation o where o.state=?1 and type in (2,3)", nativeQuery = true)
    public List<Donation> getCustomer(Integer state);

/*    @Modifying
    @Query(value = "update f_donation o set o.state = 8 where o.state in (3,11) and o.factory_id = ?1 and o.create_time between ?2 and ?3", nativeQuery = true)
    public int updateFactoryTodayOrderState(Long factoryId, String startTime, String endTime);

    @Modifying
    @Query(value = "update f_donation o set o.f_state = 8 where o.f_state=3 and o.facilitator_id = ?1 and o.create_time between ?2 and ?3", nativeQuery = true)
    public int updateFacilitatorTodayOrderState(Long facilitatorId, String startTime, String endTime);*/

    /**已有的检测场所属省份**/
    @Query(value="select id,sum(case state when '2' then ga_amount else 0 end )as noconsume,sum(case state when '3' then ga_amount else 0 end)as waitconsume,sum(case state when '8' then ga_amount else 0 end)as hasconsume from sht_order WHERE factory_id=?1", nativeQuery = true)
    List<Object[]> getMoney4Factory(Long factoryId);
    /**查询state为2，3，8的记录行数*/
    @Query(value="select count(*) from f_donation where factory_id=?1 and state in ('2','3','8')",nativeQuery=true)
    public int getRowsByFactoryId(Long factoryId);

    @Query(value="select id,sum(case state when '2' then ga_amount else 0 end )as noconsume,sum(case state when '3' then ga_amount else 0 end)as waitconsume,sum(case state when '8' then ga_amount else 0 end)as hasconsume from sht_order WHERE facilitator_id=?1", nativeQuery = true)
    List<Object[]> getMoney4Facilitator(Long facilitatorId);
    /**查询state为2，3，8的记录行数*/
    @Query(value="select count(*) from f_donation where facilitator_id=?1 and state in ('2','3','8')",nativeQuery=true)
    public int getRowsByFacilitatorId(Long facilitatorId);

    @Query(value = "select * from f_donation where service_id=?1", nativeQuery = true)
    public Donation findByService(Long serviceId);

    @Query(value="select count(*) FROM f_donation s WHERE s.state= 1",nativeQuery = true)
    int getCountByWait();


}
